﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using Model;

namespace Data
{
    public class ClienteDataAccess
    {
        private SQLiteConnection con = DataBaseHelper.GetConnection("BDHostal.sqlite");
        private bool exitoRegistrar;
        private DataTable dt = new DataTable();
        private DataSet dataSet = new DataSet();
        public DataTable buscarDatosLimitado(string persona)
        {
            persona = persona.ToLowerInvariant();
            string cmd = "SELECT c.numeroDocumento,nombre,apellido FROM clientes c WHERE (nombre like '" + persona + "%' OR nombre like '% " + persona + "%' OR apellido like '" + persona + "%' OR apellido like '% " + persona + "%') AND c.numeroDocumento NOT IN (SELECT numeroDocumento FROM hospedajes WHERE activo='si') ORDER BY nombre";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }

        public DataTable buscarDatosLimitadoReserva(string persona)
        {
            persona = persona.ToLowerInvariant();
            string cmd = "SELECT c.numeroDocumento,nombre,apellido FROM clientes c WHERE (nombre like '" + persona + "%' OR nombre like '% " + persona + "%' OR apellido like '" + persona + "%' OR apellido like '% " + persona + "%') ORDER BY nombre";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }

        public DataTable buscarDatos(string numeroDocumento)
        {
            string cmd = "SELECT numeroDocumento,nombre,apellido FROM clientes WHERE numeroDocumento = '" + numeroDocumento + "'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }

        public DataTable buscarDatosC(string numeroDocumento)
        {
            string cmd = "SELECT * FROM clientes WHERE numeroDocumento = '" + numeroDocumento + "'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }

        public DataTable buscarDatosTodos(string numeroDocumento)
        {
            string cmd = "SELECT * FROM clientes WHERE numeroDocumento = '" + numeroDocumento + "'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }

        public bool registrarDatos(string numeroDocumento, string nombre, string apellido, string email, string telefono, string celular, string notas)
        {
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    con.Open();
                    cmd.CommandText = "INSERT INTO clientes(numeroDocumento,nombre,apellido,email,telefono,celular,Notas) VALUES ('" + numeroDocumento + "','" + nombre.ToLowerInvariant() + "','" + apellido.ToLowerInvariant() + "','" + email + "','" + telefono + "','" + celular + "','" + notas + "')";
                    cmd.ExecuteNonQuery();
                    exitoRegistrar = true;

                }
                catch (SQLiteException ex)
                {

                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                    exitoRegistrar = false;
                }
                finally
                {
                    cmd.Dispose();
                    con.Close();
                }
            }
            return exitoRegistrar;
        }

        public bool buscarNumeroDocumento(string numeroDocumento)
        {
            bool resultado;
            string cmd = "SELECT numeroDocumento FROM clientes WHERE numeroDocumento='" + numeroDocumento + "'";
            {
                try
                {
                    con.Open();
                    DataSet dataSet = new DataSet();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    DataTable dt = new DataTable();
                    dt = dataSet.Tables[0];
                    DataRow dataRow;
                    if (!dt.Rows[0].Equals(null))
                    {
                        dataRow = dt.Rows[0];
                        resultado = true;
                    }
                    else
                        resultado = false;


                }
                catch (SystemException ex)
                {
                    resultado = false;
                }
                finally
                {
                    con.Dispose();
                    con.Close();
                }
            }
            return resultado;
        }

        public void registrarCliente(cliente c)
        {
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    con.Open();
                    cmd.CommandText = "INSERT INTO clientes(numeroDocumento,nombre,apellido,email,telefono,celular,notas,tipoDocumento,sexo,profesion,pais,ciudad,fecNac) VALUES ('" + c.nDocumento + "','" + c.nombres + "','" + c.apellidos + "','" + c.email + "','" + c.telefono + "','" + c.celular + "','" + c.notas + "','" + c.tDocumento + "','" + c.sexo + "','" + c.profesion + "','" + c.pais + "','" + c.ciudad + "','" + c.fecnac + "')";
                    cmd.ExecuteNonQuery();

                }
                catch (SQLiteException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                    throw new Exception("Error Registrando Cliente.");
                }
                finally
                {
                    cmd.Dispose();
                    con.Close();
                }
            }
        }

        public DataTable buscarClientes()
        {
            string cmd = "SELECT numeroDocumento,nombre,apellido FROM clientes";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
                finally
                {
                    con.Close();
                }
            }
            return dt;
        }

        public bool existeCliente(string num)
        {
            string cmd = "SELECT * FROM clientes WHERE numeroDocumento = '" + num + "'";
            bool resultado = false;

            try
            {
                con.Open();
                SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                dataSet.Reset();
                dataAdapter.Fill(dataSet);
                dt = dataSet.Tables[0];
                if (dt.Rows.Count > 0)
                    resultado = true;
                else
                    resultado = false;


            }
            catch (SystemException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return resultado;
        }

        public void modificarCliente(cliente c)
        {
            
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    con.Open();
                    cmd.CommandText = "UPDATE clientes SET nombre='" + c.nombres + "',apellido='" + c.apellidos + "',email='" + c.email + "',telefono='" + c.telefono + "',celular='" + c.celular + "',notas='" + c.notas + "',tipoDocumento='" + c.tDocumento + "',sexo='" + c.sexo + "',profesion='" + c.profesion + "',pais='" + c.pais + "',ciudad='" + c.ciudad + "',fecNac='" + c.fecnac + "' WHERE numeroDocumento='" + c.nDocumento + "'";
                    cmd.ExecuteNonQuery();

                }
                catch (SQLiteException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                    throw new Exception("Error Modificando Cliente.");
                }
                finally
                {
                    cmd.Dispose();
                    con.Close();
                }
            }
        }
    }
}
